import { BusinessException } from '../../types/exceptions.js';
import { SysDisable } from '../../enums/system.js';
import { Pool as MySQLPool } from 'mysql2/promise';
import { Pool as PgPool } from 'pg';
import {
  DatabaseOperations,
  DatabaseOperationsFactory,
  BaseMySQLOperations,
  BasePostgreSQLOperations,
} from '../../utils/database-operations.js';
import {
  SysRole,
  CreateSysRoleDto,
  UpdateSysRoleDto,
  SysRoleQueryDto,
} from '../../entities/system/SysRole.js';

// SysRole特定的数据库操作接口
interface SysRoleOperations extends DatabaseOperations<SysRole> {
  loadRoleByKey(roleKey: string): Promise<SysRole | null>;
  getRolesByType(roleType: number): Promise<SysRole[]>;
}

// MySQL数据库操作实现
class MySQLOperations extends BaseMySQLOperations<SysRole> implements SysRoleOperations {
  constructor(pool: MySQLPool) {
    super(pool);
  }

  async create(role: CreateSysRoleDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_role (
                      role_name, role_key, role_type, remark, status,
                      is_sys, time, update_time
                  ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;
    const params: (string | number)[] = [
      role.role_name as string,
      role.role_key as string,
      role.role_type as number,
      role.remark as string,
      role.status as number,
      role.is_sys as number,
      now,
      now,
    ];
    return this.executeInsert(sql, params);
  }

  async update(id: number, role: UpdateSysRoleDto): Promise<boolean> {
    const updateFields: string[] = [];
    const values: (string | number)[] = [];

    Object.entries(role).forEach(([key, value]) => {
      if (value !== undefined && value !== null && key !== 'id') {
        updateFields.push(`${key} = ?`);
        values.push(value as string | number);
      }
    });

    if (updateFields.length === 0) {
      throw new BusinessException('没有要更新的字段');
    }

    updateFields.push('update_time = ?');
    values.push(Date.now());
    values.push(id);

    const sql = `UPDATE sys_role SET ${updateFields.join(', ')} WHERE id = ? AND disabled = ${SysDisable.ENABLE}`;
    return (await this.executeUpdate(sql, values)) > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = `UPDATE sys_role SET disabled = ${SysDisable.DISABLE}, update_time = ? WHERE id = ?`;
    return (await this.executeUpdate(sql, [Date.now(), id])) > 0;
  }

  async loadRoleByKey(roleKey: string): Promise<SysRole | null> {
    const sql = 'SELECT * FROM sys_role WHERE role_key = ? AND disabled = ?';
    return this.queryOne(sql, [roleKey, SysDisable.ENABLE]);
  }

  async getRolesByType(roleType: number): Promise<SysRole[]> {
    const sql = 'SELECT * FROM sys_role WHERE role_type = ? AND disabled = ? ORDER BY id ASC';
    return this.queryMany(sql, [roleType, SysDisable.ENABLE]);
  }

  async getById(id: number): Promise<SysRole | null> {
    const sql = 'SELECT * FROM sys_role WHERE id = ? AND disabled = ?';
    return this.queryOne(sql, [id, SysDisable.ENABLE]);
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysRoleQueryDto = {}
  ): Promise<{
    list: SysRole[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = [`disabled = ${SysDisable.ENABLE}`];
    const params: (string | number)[] = [];

    // 动态构建查询条件
    if (conditions.role_name) {
      whereConditions.push('role_name LIKE ?');
      params.push(`%${conditions.role_name}%`);
    }
    if (conditions.role_key) {
      whereConditions.push('role_key LIKE ?');
      params.push(`%${conditions.role_key}%`);
    }
    if (conditions.role_type !== undefined) {
      whereConditions.push('role_type = ?');
      params.push(conditions.role_type);
    }
    if (conditions.status !== undefined) {
      whereConditions.push('status = ?');
      params.push(conditions.status);
    }
    if (conditions.is_sys !== undefined) {
      whereConditions.push('is_sys = ?');
      params.push(conditions.is_sys);
    }

    const whereClause = whereConditions.join(' AND ');

    // 查询总数
    const countResult = await this.queryOne<{ total: number }>(
      `SELECT COUNT(*) as total FROM sys_role WHERE ${whereClause}`,
      params
    );
    const total = countResult?.total || 0;

    // 查询列表
    const offset = (page - 1) * pageSize;
    const list = await this.queryMany(
      `SELECT * FROM sys_role WHERE ${whereClause} ORDER BY time DESC LIMIT ? OFFSET ?`,
      [...params, pageSize, offset]
    );

    return {
      list,
      total,
      page,
      pageSize,
    };
  }
}

// PostgreSQL数据库操作实现
class PostgreSQLOperations extends BasePostgreSQLOperations<SysRole> implements SysRoleOperations {
  constructor(pool: PgPool) {
    super(pool);
  }

  async create(role: CreateSysRoleDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_role (
                   role_name, role_key, role_type, remark, status,
                   is_sys, time, update_time
                 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
                 RETURNING id`;
    const params: (string | number)[] = [
      role.role_name as string,
      role.role_key as string,
      role.role_type as number,
      role.remark as string,
      role.status as number,
      role.is_sys as number,
      now,
      now,
    ];
    return this.executeInsert(sql, params);
  }

  async update(id: number, role: UpdateSysRoleDto): Promise<boolean> {
    const updateFields: string[] = [];
    const values: (string | number)[] = [];
    let paramIndex = 1;

    Object.entries(role).forEach(([key, value]) => {
      if (value !== undefined && key !== 'id') {
        updateFields.push(`${key} = $${paramIndex++}`);
        values.push(value as string | number);
      }
    });

    if (updateFields.length === 0) {
      throw new BusinessException('没有要更新的字段');
    }

    updateFields.push(`update_time = $${paramIndex++}`);
    values.push(Date.now());
    values.push(id);

    const sql = `UPDATE sys_role SET ${updateFields.join(', ')} WHERE id = $${paramIndex} AND disabled = ${SysDisable.ENABLE}`;
    return (await this.executeUpdate(sql, values)) > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = `UPDATE sys_role SET disabled = ${SysDisable.DISABLE}, update_time = $1 WHERE id = $2`;
    return (await this.executeUpdate(sql, [Date.now(), id])) > 0;
  }

  async loadRoleByKey(roleKey: string): Promise<SysRole | null> {
    const sql = 'SELECT * FROM sys_role WHERE role_key = $1 AND disabled = $2';
    return this.queryOne(sql, [roleKey, SysDisable.ENABLE]);
  }

  async getRolesByType(roleType: number): Promise<SysRole[]> {
    const sql = 'SELECT * FROM sys_role WHERE role_type = $1 AND disabled = $2 ORDER BY id ASC';
    return this.queryMany(sql, [roleType, SysDisable.ENABLE]);
  }

  async getById(id: number): Promise<SysRole | null> {
    const sql = 'SELECT * FROM sys_role WHERE id = $1 AND disabled = $2';
    return this.queryOne(sql, [id, SysDisable.ENABLE]);
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysRoleQueryDto = {}
  ): Promise<{
    list: SysRole[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = [`disabled = ${SysDisable.ENABLE}`];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    // 动态构建查询条件
    if (conditions.role_name) {
      whereConditions.push(`role_name LIKE $${paramIndex++}`);
      params.push(`%${conditions.role_name}%`);
    }
    if (conditions.role_key) {
      whereConditions.push(`role_key LIKE $${paramIndex++}`);
      params.push(`%${conditions.role_key}%`);
    }
    if (conditions.role_type !== undefined) {
      whereConditions.push(`role_type = $${paramIndex++}`);
      params.push(conditions.role_type);
    }
    if (conditions.status !== undefined) {
      whereConditions.push(`status = $${paramIndex++}`);
      params.push(conditions.status);
    }
    if (conditions.is_sys !== undefined) {
      whereConditions.push(`is_sys = $${paramIndex++}`);
      params.push(conditions.is_sys);
    }

    const whereClause = whereConditions.join(' AND ');

    // 查询总数
    const countResult = await this.queryOne<{ total: number }>(
      `SELECT COUNT(*) as total FROM sys_role WHERE ${whereClause}`,
      params
    );
    const total = countResult?.total || 0;

    // 查询列表
    const offset = (page - 1) * pageSize;
    const list = await this.queryMany(
      `SELECT * FROM sys_role WHERE ${whereClause} ORDER BY time DESC LIMIT $${paramIndex++} OFFSET $${paramIndex}`,
      [...params, pageSize, offset]
    );

    return {
      list,
      total,
      page,
      pageSize,
    };
  }
}

export class SysRoleService {
  private dbOperations: SysRoleOperations | null = null;

  constructor() {
    // 延迟初始化，避免在应用启动时调用context方法
  }

  private getDbOperations(): SysRoleOperations {
    if (!this.dbOperations) {
      this.dbOperations = DatabaseOperationsFactory.create<SysRoleOperations>(
        MySQLOperations,
        PostgreSQLOperations
      );
    }
    return this.dbOperations;
  }

  /**
   * 创建角色
   * @param role 角色信息
   * @returns 创建的角色ID
   */
  async create(role: CreateSysRoleDto): Promise<number> {
    return this.getDbOperations().create(role);
  }

  /**
   * 更新角色信息
   */
  async update(id: number, role: UpdateSysRoleDto): Promise<boolean> {
    return this.getDbOperations().update(id, role);
  }

  // 删除角色（软删除）
  async delete(id: number): Promise<boolean> {
    return this.getDbOperations().delete(id);
  }

  /**
   * 根据角色Key加载角色
   * @param roleKey 角色Key
   * @returns
   */
  async loadRoleByKey(roleKey: string): Promise<SysRole | null> {
    return this.getDbOperations().loadRoleByKey(roleKey);
  }

  /**
   * 根据角色类型获取角色列表
   * @param roleType 角色类型: 0 后台角色 1前台角色
   * @returns
   */
  async getRolesByType(roleType: number): Promise<SysRole[]> {
    return this.getDbOperations().getRolesByType(roleType);
  }

  /**
   * 根据ID获取角色详情
   * @param id 角色ID
   * @returns
   */
  async getById(id: number): Promise<SysRole | null> {
    return this.getDbOperations().getById(id);
  }

  // 获取角色列表
  async list(
    page: number = 1,
    pageSize: number = 10,
    query: SysRoleQueryDto = {}
  ): Promise<{ total: number; list: SysRole[]; page: number; pageSize: number }> {
    return this.getDbOperations().list(page, pageSize, query);
  }
}
